package it.univaq.disim.mwt.business.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import it.univaq.disim.mwt.business.MaterieService;
import it.univaq.disim.mwt.business.model.Classe;
import it.univaq.disim.mwt.business.model.Insegnante;
import it.univaq.disim.mwt.business.model.Materia;
import it.univaq.disim.mwt.business.model.TipologiaLaboratorio;

public class JDBCMateriaService implements MaterieService{
	
	//FUNZIONA
	@Override
	public Materia findMateriaById(Long id) {
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		String sql = "select * from materie where id = ?";
		Materia materie = null;
		
		try {
			
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setLong(1, id);
			preparedStatement.executeQuery();
			resultSet = preparedStatement.getResultSet();
			resultSet.next();
			
			Long ide= resultSet.getLong("id");
			String nome=resultSet.getString("nome");
			Long idProfessore= resultSet.getLong("id_professore");
			Long idClasse=resultSet.getLong("id_classe");
			String programma=resultSet.getString("programma");
			
			JDBCInsegnanteService jdbcInsegnanteService= new JDBCInsegnanteService();
			Insegnante insegnanti= jdbcInsegnanteService.findInsegnanteById(idProfessore);
			
			JDBCClasseService jdbcClasseService= new JDBCClasseService();
			Classe classi=jdbcClasseService.findClassiById(idClasse);
			
			materie = new Materia(ide,nome,insegnanti,programma,classi);
			
			resultSet.close();
			preparedStatement.close();
			connection.close();
			
			return materie;

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}

	//FUNZIONA
	@Override
	public List<Materia> findAllMaterie() {
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		List<Materia> listMaterie = new ArrayList<Materia>();
		String sql = " select * from materie ";
		Materia materie = null;
		
		try {
			
			connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "school", "school");
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.executeQuery();
			resultSet = preparedStatement.getResultSet();
			
			while(resultSet.next()){
				
				Long ide= resultSet.getLong("id");
				String nome=resultSet.getString("nome");
				Long idProfessore= resultSet.getLong("id_professore");
				Long idClasse=resultSet.getLong("id_classe");
				String programma=resultSet.getString("programma");
				
				JDBCInsegnanteService jdbcInsegnanteService= new JDBCInsegnanteService();
				Insegnante insegnanti= jdbcInsegnanteService.findInsegnanteById(idProfessore);
				
				JDBCClasseService jdbcClasseService= new JDBCClasseService();
				Classe classi=jdbcClasseService.findClassiById(idClasse);
				
				materie = new Materia(ide,nome,insegnanti,programma,classi);
	            listMaterie.add(materie);
			}
			
			resultSet.close();
			preparedStatement.close();
			connection.close();
		
			return listMaterie;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

		return null;
	}

}